package com.example.project.dao;

import com.example.project.pojo.Car;
import com.example.project.pojo.Parking;
import com.example.project.pojo.ParkingType;
import com.example.project.pojo.User;
import com.example.project.util.DBHelper;

import java.sql.*;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ParkingDAO {
    private final ParkingTypeDAO parkingTypeDAO = new ParkingTypeDAO();

    //1

    public void AdminCarAdd(Parking parking)
    {
        Connection connection= DBHelper.getConnection();
        PreparedStatement ps =null;
        String sql = "insert into parking(region,type_id,beizhu,status) values(?,?,?,?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1,parking.getRegion());
            ps.setString(2,parking.getParkingType().getTypeName());
            ps.setString(3,parking.getBeizhu());
            ps.setBoolean(4,true);
            ps.executeUpdate();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            DBHelper.close(ps,connection);
        }

    }



    public List<Parking> AdminCarSelect(Parking parking)
    {
        List<Parking> parkings = new ArrayList<>();

        Connection connection= DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs= null;

        String sql = "select *from parking";

        try {
            ps=  connection.prepareStatement(sql);

            rs = ps.executeQuery();

            while (rs.next())
            {
                Parking p = new Parking();

                Integer id = rs.getInt("id");
                //int car_id = ;
                p.setRegion(rs.getString("region"));
                p.setEndTime( rs.getDate("end_time"));
                p.setStatus(rs.getInt("status") == 1);



                CarDAO carDAO =new CarDAO();
                ParkingDAO parkingDAO =new ParkingDAO();
                Car car  = carDAO.findCarByID(rs.getInt("car_id"));
                ParkingType pk =(parkingTypeDAO.viewUpParkingType(rs.getInt("type_id")));
                p.setBeizhu(rs.getString("beizhu"));
                p.setParkingType(pk);
                p.setId(id);
                p.setCar(car);
                    // car.setId(rs.getInt("car.id"));
                parkings.add(p);
            }
            return parkings;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        finally {
            DBHelper.close(rs,ps,connection);
        }
        return null;

    }
    public List<Parking> FindAllBeforeLease(){
        List<Parking> parkingList =new ArrayList<>();
        Connection connection= DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        String sql ="select * from parking where status=1";
        try {
            ps=connection.prepareStatement(sql);
            rs= ps.executeQuery();
            while(rs.next()){
                Parking parking = new Parking();
                int id = rs.getInt("id");
                parkingSet(id, rs, parkingList, parking, parkingTypeDAO);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(rs,ps,connection);
        }

        return parkingList;
    }
    public void lease(Parking parking){
        Connection connection =   DBHelper.getConnection();
        PreparedStatement ps =null;
        String sql="update  parking set car_id=?,end_time=?,status=? where id=?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setInt(1,parking.getCar().getId());
            ps.setDate(2,parking.getEndTime());
            ps.setBoolean(3,false);
            ps.setInt(4,parking.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(ps,connection);
        }
    }
    public List<Parking> checkBeforeLeaseByRegion(String region){
        Connection connection =   DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        List<Parking> parkingList =new ArrayList<>();
        String sql ="select * from parking where region like  ? and status =?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setString(1,"%"+region+"%");
            ps.setBoolean(2,true);
            rs=ps.executeQuery();
            while (rs.next()){
                Parking parking =new Parking();
                parkingSet(rs.getInt("id"),rs,parkingList,parking,parkingTypeDAO);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(rs,ps,connection);
        }
        return parkingList;
    }
    public List<Parking> checkBeforeLeaseByBeiZhu(String beizhu){
        Connection connection =   DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        List<Parking> parkingList =new ArrayList<>();
        String sql ="select * from parking where beizhu like ? and status =?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setString(1,"%"+beizhu+"%");
            ps.setBoolean(2,true);
            rs=ps.executeQuery();
            while (rs.next()){
                Parking parking =new Parking();
                parkingSet(rs.getInt("id"),rs,parkingList,parking,parkingTypeDAO);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(rs,ps,connection);
        }
        return parkingList;
    }
    public List<Parking> checkBeforeLeaseByType(String typename){
        Connection connection =   DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        List<Parking> parkingList =new ArrayList<>();
        String sql="select * from parking where type_id in (select id from parking_type where type_name like ?) and status=?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setString(1,"%"+typename+"%");
            ps.setBoolean(2,true);
            rs= ps.executeQuery();
            while (rs.next()){
                Parking parking = new Parking();

                parkingSet(rs.getInt("id"), rs, parkingList, parking, parkingTypeDAO);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(rs,ps,connection);
        }
        return parkingList;
    }
    public List<Parking> checkBeforeLeaseById(Integer id){
        Connection connection =   DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        List<Parking> parkingList =new ArrayList<>();
        String sql="select * from parking where id=? and status=?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ps.setBoolean(2,true);
            rs=  ps.executeQuery();
            while(rs.next()){
                Parking parking = new Parking();
                parkingSet(id, rs, parkingList, parking, parkingTypeDAO);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        finally {
            DBHelper.close(rs,ps,connection);
        }
        return parkingList;
    }

    private void parkingSet(Integer id, ResultSet rs, List<Parking> parkingList, Parking parking, ParkingTypeDAO parkingTypeDAO) throws SQLException {
        parking.setRegion(rs.getString("region"));
        ParkingType pk =parkingTypeDAO.viewUpParkingType(rs.getInt("type_id"));
        parking.setBeizhu(rs.getString("beizhu"));
        parking.setParkingType(pk);
        parking.setId(id);
        parking.setTimeRemaining(day(rs.getDate("end_time")));
        parking.setStatus(true);
        parkingList.add(parking);
    }

    //前端车主更改车位信息
    public void upParkingLicense(String license, int parkingId) {
        Connection connection = DBHelper.getConnection();
        PreparedStatement preparedStatement = null;

        String sql = "UPDATE parking\n" +
                "SET car_id=(select id from car where license=?)\n" +
                "WHERE id=?";
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1, license);
            preparedStatement.setInt(2, parkingId);

            preparedStatement.executeUpdate();

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            DBHelper.close(preparedStatement, connection);
        }
    }

    public List<Parking> showPrivateParking(User user) {
        List<Parking> List = new ArrayList<>();
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "SELECT  *" +
                    "FROM user,car,parking,parking_type " +
                    "WHERE user.id=car.user_id AND car.id=parking.car_id AND parking_type.id=parking.type_id AND car.user_id=?";

            ps = conn.prepareStatement(sql);
            ps.setInt(1, user.getId());
            rs = ps.executeQuery();

            while (true) {
                boolean flag = rs.next();
                if (!flag) {
                    break;
                }
                Car car = new Car();
                car.setId(rs.getInt("car.id"));
                car.setUser(user);
                car.setLicense(rs.getString("license"));
                ParkingType parkingType = new ParkingType();
                parkingType.setTypeName(rs.getString("type_name"));
                Parking parking = new Parking();
                parking.setId(rs.getInt("parking.id"));
                parking.setCar(car);
                parking.setParkingType(parkingType);
                parking.setRegion(rs.getString("region"));
                int rtime = day(rs.getDate("end_time"));
                parking.setTimeRemaining(rtime);
                List.add(parking);
            }

            return List;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBHelper.close(rs, ps, conn);
        }
        return null;
    }

    public int day(java.util.Date date) {

        DateFormat dft = new SimpleDateFormat("yyyy-MM-dd");

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date timeday = new java.util.Date();
        try {
            java.util.Date star = dft.parse(format.format(timeday));//开始时间
            Date endDay = dft.parse(String.valueOf(date));//结束时间
            Long starTime = star.getTime();
            Long endTime = endDay.getTime();
            Long num = endTime - starTime;//时间戳相差的毫秒数
//            System.out.println("相差天数为："+);//除以一天的毫秒数
            int rtime = (int) (num / 24 / 60 / 60 / 1000);
            if (rtime > 0) {
                return rtime;
            } else {
                return 0;
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }

    //查看到期时间
    public int FindEndTime(java.util.Date date){
        //通过车辆id查询,临期用户
        DateFormat dft = new SimpleDateFormat("yyyy-MM-dd");

        SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
        java.util.Date timeday = new java.util.Date();
        try {
            java.util.Date star = dft.parse(format.format(timeday));//开始时间
            Date endDay = dft.parse(String.valueOf(date));//结束时间
            Long starTime = star.getTime();
            Long endTime = endDay.getTime();
            Long num = endTime - starTime;//时间戳相差的毫秒数
//            System.out.println("相差天数为："+);//除以一天的毫秒数
            int rtime = (int) (num / 24 / 60 / 60 / 1000);
//            int count = Integer.parseInt("未临期");
//            System.out.println("111111111");



            if (rtime <= 30) {
//                System.out.println(rtime);

                return rtime;
            } else {
                return rtime;
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;

    }
    public List<Parking> showEndTime() {
        List<Parking> List = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT user.id,car.id,parking.id,parking_type.id " +
                "from user,car,parking,parking_type where parking.car_id=car.id \n" +
                "AND car.user_id=user.id AND parking.type_id=parking_type.id AND status=?";
        try {
            ps = connection.prepareStatement(sql);
            ps.setInt(1,0);
            rs = ps.executeQuery();
            while (rs.next()) {
              UserDAO userDAO =new UserDAO();
              CarDAO carDAO =new CarDAO();

              User user =userDAO.findUserByID(rs.getInt("user.id"));
                Car car = carDAO.findCarByID(rs.getInt("car.id"));
                Parking parking = findParkingByID(rs.getInt("parking.id"));
                car.setUser(user);
                parking.setCar(car);
                int rtime = FindEndTime(parking.getEndTime());
                parking.setTimeRemaining(rtime);
                String parkingStatus = pkingStatus(parking.isStatus());
                parking.setPkStatus(parkingStatus);
                ParkingType parkingType = parkingTypeDAO.viewUpParkingType(rs.getInt("parking_type.id"));
                parking.setParkingType(parkingType);
                if(rtime<=30){
                    List.add(parking);
                }
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(rs, ps, connection);
        }
        return List;
    }

    //前端显示要改个人车位车牌号
    public Parking showLicense(int pId) {
        Connection conn = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            String sql = "select *" +
                    "FROM user,car,parking,parking_type WHERE user.id=car.user_id AND car.id=parking.car_id AND parking_type.id=parking.type_id  AND parking.id=?";
 
            ps = conn.prepareStatement(sql);
            ps.setInt(1, pId);
            rs = ps.executeQuery();
            rs.next();
            User user = new User();
            user.setUserName(rs.getString("username"));
            Car car = new Car();
            car.setUser(user);
            car.setId(rs.getInt("car.id"));
            car.setLicense(rs.getString("license"));
            ParkingType parkingType = new ParkingType();
            parkingType.setTypeName(rs.getString("type_name"));
            Parking parking = new Parking();
            parking.setCar(car);
            parking.setParkingType(parkingType);
            parking.setId(rs.getInt("parking.id"));
            parking.setRegion(rs.getString("region"));
            int rtime = day(rs.getDate("end_time"));
            parking.setTimeRemaining(rtime);
            return parking;

        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            DBHelper.close(rs, ps, conn);
        }
        return null;
    }
    public void renewal(Parking parking){
       Connection connection= DBHelper.getConnection();
       PreparedStatement ps =null;
       String sql ="update parking set end_time = ? where id = ?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setDate(1,parking.getEndTime());
            ps.setInt(2,parking.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(ps,connection);
        }
    }


    public String pkingStatus(boolean bl) {
        String pkStatus = "";

        if (bl) {
            pkStatus = "可租";
        } else {
            pkStatus = "不可租";
        }
        return pkStatus;
    }

    public List<Parking> showPriParking() {
        List<Parking> List = new ArrayList<>();
        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = "SELECT user.username,user.userphone,car.license,parking.region,parking.end_time,parking.status,parking_type.type_name " +
                "from user,car,parking,parking_type where parking.car_id=car.id \n" +
                "AND car.user_id=user.id AND parking.type_id=parking_type.id and parking.status=0";
        try {
            ps = connection.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                User user = new User();
                user.setUserName(rs.getString("username"));
                user.setUserPhone(rs.getString("userphone"));
                Car car = new Car();
                Parking parking = new Parking();
                car.setUser(user);
                car.setLicense(rs.getString("license"));
                parking.setCar(car);
                parking.setRegion(rs.getString("region"));
                int rtime = day(rs.getDate("end_time"));
                parking.setTimeRemaining(rtime);
                String parkingStatus = pkingStatus(rs.getBoolean("status"));
                parking.setPkStatus(parkingStatus);
                ParkingType parkingType = new ParkingType();
                parkingType.setTypeName(rs.getString("type_name"));
                parking.setParkingType(parkingType);
                List.add(parking);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(rs, ps, connection);
        }
        return List;
    }
    //通过id 查找车位
    public Parking findParkingByID(Integer id){
        Connection conn =  DBHelper.getConnection();
        PreparedStatement ps =null;
        ResultSet rs =null;
        Parking parking =new Parking();
        String sql ="Select * from parking where id=?";
        try {
            ps =conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs= ps.executeQuery();
           while(rs.next()){
               parking.setId(id);
               CarDAO carDAO =new CarDAO();
               parking.setCar(carDAO.findCarByID(rs.getInt("car_id")));
               parking.setStatus(rs.getInt("status")==1);
               parking.setEndTime(rs.getDate("end_time"));
               parking.setTimeRemaining(day(parking.getEndTime()));
               parking.setRegion(rs.getString("region"));
               parking.setParkingType(parkingTypeDAO.viewUpParkingType(rs.getInt("type_id")));
               parking.setBeizhu(rs.getString("beizhu"));
           }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(rs,ps,conn);
        }

        return  parking;
    }

    public List<Car> showAllLicense(String license) {
        Connection connection = DBHelper.getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Car> licenseList=new ArrayList<>();
        String sql="SELECT distinct license,id FROM car WHERE user_id=(SELECT distinct user_id FROM car WHERE license=?)";
        try {
            ps = connection.prepareStatement(sql);
            ps.setString(1, license);
            rs = ps.executeQuery();
            while (true) {
                boolean flag = rs.next();
                if (!flag) {
                    break;
                }
                Car car=new Car();
                car.setId(rs.getInt("id"));
                car.setLicense(rs.getString("license"));
                licenseList.add(car);
            }

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(rs, ps, connection);
        }

        return licenseList;
    }
    public void  deletParking(Integer id){
       Connection connection = DBHelper.getConnection();
       PreparedStatement ps =null;
       String sql ="delete  from parking where id =?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setInt(1,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(ps,connection);
        }

    }
    public void updateParking(Parking parking){
        Connection connection =DBHelper.getConnection();
        PreparedStatement ps =null;
        String sql ="update parking set region=?,status=?,beizhu=?,type_id=? where id =?";
        try {
            ps=connection.prepareStatement(sql);
            ps.setString(1,parking.getRegion());
            ps.setBoolean(2,parking.isStatus());
            ps.setString(3,parking.getBeizhu());
            ps.setInt(4,parking.getParkingType().getId());
            ps.setInt(5,parking.getId());
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBHelper.close(ps,connection);
        }
    }
    //修改退租状态
    public void updateStatus(Integer id)
    {

        Connection con =DBHelper.getConnection();
        PreparedStatement  ps =null;
        String sql ="update parking set end_time = ?, status = ? ,car_id = ? WHERE id=?";
        try {
            ps=con.prepareStatement(sql);
            ps.setNull(1, Types.DATE);
            ps.setBoolean(2,true);
          ps.setNull(3, Types.INTEGER);
          ps.setInt(4,id);
            ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBHelper.close(ps,con);
        }
    }
}
